<?php

namespace App\Http\Controllers\Test;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;

class DatabaseController extends Controller
{

    // 备份
    // http://slh.com/test/database/bak
    public function bak()
    {
        $db_name = 'laravel_slh';
        $db_sql  = "SHOW DATABASES LIKE `".$db_name."`;";
        $db_sql  = "select TABLE_NAME from information_schema.tables where table_schema='".$db_name."';";
        $db_info = DB::select($db_sql);
        if(empty($db_info)){
            $this->error = '数据库不存在！！';

            return false;
        }

        ## 所有数据
        $cline      = chr(10);
        $tabels_sql = "show tables from `$db_name`;";
        $tabels_sql = "select table_name from information_schema.tables where table_schema='ctsnews' and table_type='base table';";
        $tabel_list = DB::select($tabels_sql);

        ## 组合导出的sql语句
        $bak_sql = "-- ----------------------------".$cline;
        $bak_sql .= "-- 日期：".date("Y-m-d H:i:s", time()).$cline;
        $bak_sql .= "-- 本程序由小学生制作,本程序不适合处理超大量数据".$cline;
        $bak_sql .= "-- ----------------------------".$cline.$cline;
        $bak_sql .= "-- ----------------------------".$cline;
        foreach($tabel_list as $table){
            ## 获取数据表结构
            $table_name = $table['table_name'];
            $_structure = DB::select("SHOW CREATE TABLE `$db_name`.`$table_name`;"); // 多列
            $_structure = $_structure[0]; // shtmlspecialchars_decode
            $createSql  = $_structure['Create Table'];

            ## 数据表结构语句
            $bak_sql .= "-- Table structure for `".$table_name."`".$cline;
            $bak_sql .= "-- ----------------------------".$cline;
            $bak_sql .= "DROP TABLE IF EXISTS `".$table_name."`;".$cline.$cline;
            $bak_sql .= "-- ".htmlspecialchars_decode($_structure['Table']).$cline;
            $bak_sql .= htmlspecialchars_decode($_structure['Create Table']).';'.$cline;

            ## 导出数据
            ## 备份的时候 导出系统表数据的时候
            $_list   = DB::select("SELECT * FROM `$db_name`.`$table_name` limit 1000 "); //order by id asc
            $bak_sql .= "-- ----------------------------".$cline;
            $bak_sql .= "-- Records for `".$table_name."`".$cline;
            $bak_sql .= "-- ----------------------------".$cline;

            // 插入数据
            foreach($_list as $key => $row){
                $make_sql = '';
                foreach($row as $_val){
                    if(!isset($_val)){
                        $make_sql .= " NULL,";
                    } else{
                        $_val     = is_numeric($_val) ? $_val : ($_val == 0 ? $_val : addslashes($_val));
                        $make_sql .= " '".$_val."',";
                    }
                }

                $make_sql   = substr($make_sql, 0, -1); ## 取消最后一个逗号
                $insert_sql = "INSERT INTO `$table_name` VALUES   ( $make_sql );".$cline;
                $bak_sql    .= $insert_sql;
            }

            $bak_sql .= "-- ----------------------------".$cline.$cline.$cline;

            //echo ' == 备份本地数据表：'.$table_name.$cline;
        }

        ## 整理数据，生成统一文件
        //$filename = $db_name.'-'.date("Y-m-d H：i：s").".sql";
        //file_put_contents(__DIR__.'/'.$filename, $bak_sql);
        echo '请使用看源代码的方式查看！';

        //return true;
        echo $bak_sql;
    }

    // 字段信息
    // http://slh.com/test/database/field
    public function field()
    {
        $db_name = 'laravel_slh';
        $do_sql  = "SELECT `TABLE_NAME` tablename, `COLUMN_NAME` field,DATA_TYPE `type`,IS_NULLABLE is_null ,CHARACTER_MAXIMUM_LENGTH  `length`,NUMERIC_PRECISION num_length,NUMERIC_SCALE num_bit,COLUMN_COMMENT comment,COLUMN_DEFAULT `default` FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='".$db_name."'";
        $fields  = DB::select($do_sql);

        // 格式化
        //$fields = array_column($fields, null, 'field');
        $fields = array_column($fields, 'comment', 'field');

        echo json_encode($fields, JSON_UNESCAPED_UNICODE);

        //echo var_export($fields,1);

        return $this->success($fields);
    }

    // 数据库验证
    // http://slh.com/test/database/check
    public function check()
    {
        $db_name = 'laravel_slh';
        $table   = $this->getData('table', 'shl_push');
        $do_sql  = "SELECT `TABLE_NAME` tablename, `COLUMN_NAME` field,DATA_TYPE `type`,IS_NULLABLE is_null ,CHARACTER_MAXIMUM_LENGTH  `length`,NUMERIC_PRECISION num_length,NUMERIC_SCALE num_bit,COLUMN_COMMENT comment,COLUMN_DEFAULT `default` FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='".$db_name."' AND TABLE_NAME='".$table."' ";
        $fields  = DB::select($do_sql);
        $rule    = $get = [];

        // 格式化
        //$fields = array_column($fields, null, 'field');
        $fields = array_column($fields, 'comment', 'field');
        foreach($fields as $field => $val){
            $rule[$field] = 'required';
            $get[$field]  = '$this->getData("'.$field.'")';
        }
        $result = ['msg' => var_export($fields, 1), 'rule' => $rule,];

        echo json_encode($fields, JSON_UNESCAPED_UNICODE);
        echo '<br/>';
        echo '<br/>';
        echo var_export($fields, 1);
        echo '<br/>';
        echo '<br/>';
        echo var_export($get, 1);
        echo '<br/>';
        echo '<br/>';
        echo var_export($rule, 1);
        //return $this->success($result);
    }

}
